﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.IO;

public partial class SqliteCreate : System.Web.UI.Page
{
    private string dataSource = HttpContext.Current.Server.MapPath("~/App_Data/SQLiteDatabase.db");
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    /*
1、创建空的sqlite数据库。 //数据库名的后缀你可以直接指定，甚至没有后缀都可以
    方法一：创建一个空sqlite数据库，用IO的方式 FileStream fs = File.Create("c://test.db");
    方法二：用SQLiteConnection SQLiteConnection.CreateFile("c://test.db"); 创建的数据库是个0字节的文件。
2、创建加密的空sqlite数据库
         SQLiteConnection.CreateFile（"c://test2.db");
         SQLiteConnection cnn = new SQLiteConnection("Data Source=c://test2.db");
         SQLiteConnection cnn = new SQLiteConnection("Data Source=D://test2.db");
        cnn.Open(); cnn.ChangePassword("password");
 */
    protected void ButtonCreate_Click(object sender, EventArgs e)
    {
        if (File.Exists(dataSource))
        {
            LabeCreateMsg.Text = "数据库已经存在。";
            return;
        }
        else
        {
            SQLiteConnection.CreateFile(dataSource);
            SQLiteConnection conn = null;
            try
            {
                conn = new SQLiteConnection();
                SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder();
                connectionStringBuilder.DataSource = dataSource;
                //connectionStringBuilder.Password = "nest99MOVIE";//设置密码，SQLite ADO.NET实现了数据库密码保护
                conn.ConnectionString = connectionStringBuilder.ToString();
                conn.Open();
                conn.ChangePassword(TextBoxPwd.Text);//修改密码
                //conn.SetPassword(TextBoxPwd.Text);//或者设置密码
                LabeCreateMsg.Text = "创建加密数据库成功";
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        //连接数据库
        //SQLiteConnection conn = new SQLiteConnection();
        //SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
        //connstr.DataSource = datasource;
        //connstr.Password = "nest99MOVIE";//设置密码，SQLite ADO.NET实现了数据库密码保护
        //conn.ConnectionString = connstr.ToString();
        //conn.Open();
        ////创建表
        //SQLiteCommand cmd = new SQLiteCommand();
        //// string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
        ////cmd.CommandText = sql;
        //cmd.Connection = conn;
        ////cmd.ExecuteNonQuery();
        ////插入数据
        //string sql = "INSERT INTO test VALUES('a2','b2')";
        //cmd.CommandText = sql;
        //cmd.ExecuteNonQuery();
        ////取出数据
        //sql = "SELECT * FROM test";
        //cmd.CommandText = sql;
        //SQLiteDataReader reader = cmd.ExecuteReader();
        //StringBuilder sb = new StringBuilder();
        //while (reader.Read())
        //{
        //    sb.Append("username:").Append(reader.GetString(0)).Append("<br />")
        //    .Append("password:").Append(reader.GetString(1)).Append("<br />");
        //}
        //reader.Close();
        //conn.Close();
        //Label1.Text = sb.ToString();

    }
    protected void ButtonChange_Click(object sender, EventArgs e)
    {
        SQLiteConnection conn = null;
        try
        {
            conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder();
            connectionStringBuilder.DataSource = dataSource;
            connectionStringBuilder.Password = TextBoxPwdOld.Text;//"nest99MOVIE";//原密码
            conn.ConnectionString = connectionStringBuilder.ToString();
            conn.Open();
            conn.ChangePassword(TextBoxPwdNew.Text);//修改密码
            //conn.SetPassword(TextBoxPwd.Text);//或者设置密码
            LabelChangeMsg.Text = "修改成功";
        }
        catch (Exception ex)
        {
            LabelChangeMsg.Text = "修改密码出错:" + ex.ToString();
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    protected void ButtonExecuteSql_Click(object sender, EventArgs e)
    {
        string sql = TextBoxSql.Text;
        SQLiteConnection conn = null;
        try
        {
            conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder();
            connectionStringBuilder.DataSource = dataSource;
            connectionStringBuilder.Password = "nest99MOVIE";//设置密码，SQLite ADO.NET实现了数据库密码保护
            conn.ConnectionString = connectionStringBuilder.ToString();
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = cmd.ExecuteReader();
            int column = reader.FieldCount;
            int i = 0;
            while (reader.Read())
            {
                while (i < column)
                {
                    LabelSqlResult.Text += reader[i].ToString();
                    i++;
                }
                i = 0;
                LabelSqlResult.Text += "<br />";
            }
            reader.Close();
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    protected void ButtonExcuteNonQuery_Click(object sender, EventArgs e)
    {
        string sql = TextBoxSql.Text;
        SQLiteConnection conn = null;
        try
        {
            conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder();
            connectionStringBuilder.DataSource = dataSource;
            connectionStringBuilder.Password = "nest99MOVIE";//设置密码，SQLite ADO.NET实现了数据库密码保护
            conn.ConnectionString = connectionStringBuilder.ToString();
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            cmd.ExecuteNonQuery();
            LabelSqlResult.Text = "执行成功";
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}
